package com.le.tester.tip;

import com.le.tester.dto.FileDto;
import com.le.tester.dto.GenSQLDto;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * createTime：2024-08-07 9:33
 * description：连接数据库，编写sql
 */
public class DataUpdate {


    //连接驱动
    private static Connection conn;

    private static final String jdbcDriver = "com.mysql.cj.jdbc.Driver";

    private static final String dbUrl = "jdbc:mysql://112.74.75.226:3306/bojue_applets_0?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";

    private static final String user = "bojue";

    private static final String pass = "rytjMxMwRPs7sPTT";

    static {
        try {
            //注册驱动
            Class.forName(jdbcDriver);
            System.out.println("start connection DB");
            //打开连接
            conn = DriverManager.getConnection(dbUrl, user, pass);
            conn.setReadOnly(Boolean.FALSE);
            System.out.println("connection success");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //exec sql
    public static void execSql(String sql) throws Exception {
        try {
            int rowCount = conn.prepareStatement(sql).executeUpdate();
            if (rowCount < 1) {
                System.out.println("sql fail and is " + sql);
            }
            System.out.println("sql success and is " + sql);
        } catch (Exception e) {
            throw new RuntimeException(e.toString());
        } finally {
            if (Objects.nonNull(conn)) {
                conn.close();
            }

        }
    }

    //gen sql
    public static String genSql(List<FileDto> fileList, GenSQLDto genSQL) {
        String table = genSQL.getTableName();
        String column = genSQL.getColumnName();
        String onlyId = genSQL.getOnlyIdName();
        if (StringUtils.isBlank(table) || Objects.isNull(onlyId)
                || StringUtils.isBlank(column) || CollectionUtils.isEmpty(fileList)) {
            throw new IllegalArgumentException();
        }

        String idListStr = fileList.stream().map(item -> String.valueOf(item.getId())).collect(Collectors.joining(","));

        StringBuilder sql = new StringBuilder("update " + table + " set " + column + " = case");

        for (FileDto item : fileList) {
            sql.append(" when ").append(onlyId).append(" = ")
                    .append(item.getId())
                    .append(" then '")
                    .append(item.getFileUrl()).append("'");
        }
        sql.append(" else ")
                .append(column)
                .append(" end")
                //os_id in (")
                .append(" where ").append(onlyId).append(" in (")
                .append(idListStr)
                .append(")");

        return sql.toString();
    }

}
